﻿using Android_Helper.entity;
using Android_Helper.Utils;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Android_Helper.python
{
    class DescTableUtils
    {
        public static string PythonCRUD(string connectiongString, string dbName, string tableName)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.AppendLine(PythonInsertTable(connectiongString, dbName, tableName));
            stringBuilder.AppendLine(PythonInsertTableFromTHSXLSX(connectiongString, dbName, tableName));
            //stringBuilder.AppendLine(GolangRowToObject(connectiongString, dbName, tableName));
            //stringBuilder.AppendLine(GolangRowsToObject(connectiongString, dbName, tableName));
            //stringBuilder.AppendLine(GolangGetRow(connectiongString, dbName, tableName));
            //stringBuilder.AppendLine(GolangGetRows(connectiongString, dbName, tableName));
            //stringBuilder.AppendLine(GolangUpdateOrDelete(connectiongString, dbName, tableName));
            //stringBuilder.AppendLine(GolangInsert(connectiongString, dbName, tableName));
            //stringBuilder.AppendLine(GolangUpdateSQLAndParamsSegment(connectiongString, dbName, tableName));
            return stringBuilder.ToString();
        }

        private static string PythonInsertTableFromTHSXLSX(string connectiongString, string dbName, string tableName)
        {
            StringBuilder stringBuilder = new StringBuilder();
            List<TableColumnEntity> list = SchemaUtils.TableColumnToEntity(connectiongString, dbName, tableName);
            TableColumnEntity tableEntity = SchemaUtils.TableToEntity(connectiongString, dbName, tableName);
            stringBuilder.AppendLine();
            stringBuilder.AppendLine("colMap = {");
            var listcol = list.Where(item => !NameUtils.IsSystemColume(item.ColumnName)).ToList();
            for (int columnPos = 0; columnPos < listcol.Count; columnPos++)
            {
                stringBuilder.AppendLine("'"+NameUtils.LowercaseUnderlineToCammle(listcol[columnPos].ColumnName) +"': "+ columnPos.ToString()+",   #"+ NameUtils.LowercaseUnderlineToCammle(listcol[columnPos].ColumnComment));
            }
            stringBuilder.AppendLine("}");
            for (int columnPos = 0; columnPos < listcol.Count; columnPos++)
            {
                stringBuilder.AppendLine("table.cell(row = nrow, column = colMap['" + NameUtils.LowercaseUnderlineToCammle(listcol[columnPos].ColumnName) + "']).value, #" + NameUtils.LowercaseUnderlineToCammle(listcol[columnPos].ColumnComment));
            }
            stringBuilder.AppendLine();
            return stringBuilder.ToString();
        }

        public static string PythonInsertTable(string connectiongString, string dbName, string tableName) {
            StringBuilder stringBuilder = new StringBuilder();
            List<TableColumnEntity> list = SchemaUtils.TableColumnToEntity(connectiongString, dbName, tableName);
            TableColumnEntity tableEntity = SchemaUtils.TableToEntity(connectiongString, dbName, tableName);
            stringBuilder.AppendLine();
            stringBuilder.AppendLine("# insert" + NameUtils.LowercaseUnderlineToAllCapitalUpper(tableName)  + tableEntity.ColumnComment + "  Insert New Recorder");
            stringBuilder.AppendLine("def insert" + NameUtils.LowercaseUnderlineToAllCapitalUpper(tableName) + "(");
            var listcol = list.Where(item => !NameUtils.IsSystemColume(item.ColumnName)).ToList();
            string strtmp = string.Empty;
            for (int columnPos = 0; columnPos < listcol.Count; columnPos++) {
                strtmp += NameUtils.LowercaseUnderlineToCammle(listcol[columnPos].ColumnName) + ", ";
                if (columnPos>0&& columnPos % 3 == 0) {
                    strtmp+="\n";
                }
            }
            strtmp = (listcol.Count - 1) % 3 >0 ? strtmp.Remove(strtmp.Length - 2):strtmp.Remove(strtmp.Length - 3); 
            stringBuilder.AppendLine(strtmp);
            stringBuilder.AppendLine("):");
            stringBuilder.AppendLine();
            stringBuilder.AppendLine("sql = \"INSERT INTO "+tableName+"(\\");
            listcol = list.Where(item => !NameUtils.IsInsertNoColumns(item.ColumnName)).ToList();
            strtmp = string.Empty;
            for (int columnPos = 0; columnPos < listcol.Count; columnPos++)
            {
                strtmp += "`"+ listcol[columnPos].ColumnName + "`, ";
                if (columnPos > 0 && columnPos % 3 == 0)
                {
                    strtmp += " \\\n";
                }
            }
            strtmp = (listcol.Count - 1) % 3 > 0 ? strtmp.Remove(strtmp.Length - 2) : strtmp.Remove(strtmp.Length - 5);
            stringBuilder.AppendLine(strtmp+")\\");
            stringBuilder.AppendLine("values(\\");
            strtmp = string.Empty;
            for (int columnPos = 0; columnPos < listcol.Count; columnPos++)
            {
                strtmp += "'{" + columnPos.ToString() + "}', ";
                if (columnPos > 0 && columnPos % 3 == 0)
                {
                    strtmp += " \\\n";
                }
            }
            strtmp = (listcol.Count - 1) % 3 > 0 ? strtmp.Remove(strtmp.Length - 2) : strtmp.Remove(strtmp.Length - 5);
            stringBuilder.AppendLine(strtmp+ ")\".format(");
            strtmp = string.Empty;
            for (int columnPos = 0; columnPos < listcol.Count; columnPos++)
            {
                strtmp += "updateValue(" + NameUtils.LowercaseUnderlineToCammle(listcol[columnPos].ColumnName) + ", 0), ";
                if (columnPos > 0 && columnPos % 3 == 0)
                {
                    strtmp += " \n";
                }
            }
            strtmp = (listcol.Count - 1) % 3 > 0 ? strtmp.Remove(strtmp.Length - 2) : strtmp.Remove(strtmp.Length - 5);
            stringBuilder.AppendLine(strtmp+")");
            stringBuilder.AppendLine();
            stringBuilder.AppendLine();
            stringBuilder.AppendLine();
            stringBuilder.AppendLine();
            return stringBuilder.ToString();
        }

    }
}
